from django.shortcuts import render, get_object_or_404, redirect
from django.http import JsonResponse
from django.contrib import messages
from django.db import connection, transaction
from django.core.paginator import Paginator
from django.contrib.auth.decorators import login_required
from django.utils import timezone

from .models import Supermarket, SupermarketApplication


def market_list(request):
    """合作超市列表页面"""
    # 获取筛选参数
    rating_param = request.GET.get('rating')
    sort = request.GET.get('sort', 'rating')
    
    # 获取用户位置信息（如果有）
    user_lat = request.GET.get('lat')
    user_lng = request.GET.get('lng')
    
    # 基础查询
    base_query = "SELECT * FROM tb_supermarket WHERE status = 1"
    params = []
    
    # 如果请求距离排序但没有提供位置信息
    if sort == 'distance' and (not user_lat or not user_lng):
        messages.info(request, "距离排序需要位置信息，请允许位置访问或在地图上选择位置")
        sort = 'rating'  # 默认回退到评分排序
    
    # 应用筛选 - 确保rating不是None或空字符串
    if rating_param and rating_param.lower() != 'none':
        try:
            rating = float(rating_param)
            base_query += " AND rating >= %s"
            params.append(rating)
        except ValueError:
            pass
    else:
        rating_param = None  # 重置为None以便于在模板中正确显示
    
    # 应用排序
    if sort == 'rating':
        query = base_query + " ORDER BY rating DESC"
    elif sort == 'distance' and user_lat and user_lng:
        try:
            # 使用经纬度计算距离（类似nearest_markets函数的方式）
            query = base_query.replace("SELECT *", """
                SELECT *, SQRT(POWER(latitude - %s, 2) + POWER(longitude - %s, 2)) * 111 AS distance
            """) + " ORDER BY distance ASC"
            params.extend([float(user_lat), float(user_lng)])
        except ValueError:
            query = base_query + " ORDER BY rating DESC"
            messages.error(request, "位置信息格式不正确")
    elif sort == 'box_count':
        query = base_query + " ORDER BY (SELECT COUNT(*) FROM tb_blind_box WHERE market_id = tb_supermarket.market_id AND status = 1) DESC"
    else:
        query = base_query + " ORDER BY rating DESC"  # 默认排序
    
    # 执行查询
    with connection.cursor() as cursor:
        cursor.execute(query, params)
        columns = [col[0] for col in cursor.description]
        markets_data = [dict(zip(columns, row)) for row in cursor.fetchall()]
    
    # 添加盲盒数量
    for market in markets_data:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT COUNT(*) FROM tb_blind_box
                WHERE market_id = %s AND status = 1
            """, [market['market_id']])
            market['box_count'] = cursor.fetchone()[0]
    
    # 分页
    paginator = Paginator(markets_data, 12)  # 每页显示12个
    page_number = request.GET.get('page', 1)
    page_obj = paginator.get_page(page_number)
    
    context = {
        'markets': page_obj,
        'rating_filter': rating_param,
        'sort': sort,
    }
    return render(request, 'market/list.html', context)


def market_detail(request, market_id):
    """超市详情页面"""
    market = get_object_or_404(Supermarket, market_id=market_id, status=1)
    
    # 获取超市关联的盲盒
    boxes = market.get_boxes(limit=8)
    
    # 获取超市评价
    reviews = market.get_reviews(limit=5)
    
    # 获取类似超市
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT * FROM tb_supermarket
            WHERE status = 1 AND market_id != %s
            ORDER BY rating DESC
            LIMIT 4
        """, [market_id])
        columns = [col[0] for col in cursor.description]
        similar_markets = [dict(zip(columns, row)) for row in cursor.fetchall()]
    
    context = {
        'market': market,
        'boxes': boxes,
        'reviews': reviews,
        'similar_markets': similar_markets,
        'box_count': market.get_box_count(),
    }
    return render(request, 'market/detail.html', context)


@login_required
def market_apply(request):
    """合作超市申请页面"""
    if request.method == 'POST':
        name = request.POST.get('name')
        address = request.POST.get('address')
        business_hours = request.POST.get('business_hours')
        contact_name = request.POST.get('contact_name')
        contact_phone = request.POST.get('contact_phone')
        remark = request.POST.get('remark')
        lat = request.POST.get('latitude')
        lng = request.POST.get('longitude')
        
        # 简单验证
        if not all([name, address, contact_name, contact_phone]):
            messages.error(request, '请填写必填字段')
            return render(request, 'market/apply.html', {'form_data': request.POST})
        
        try:
            with transaction.atomic():
                with connection.cursor() as cursor:
                    cursor.execute("""
                        INSERT INTO tb_supermarket_application
                        (name, address, business_hours, status, 
                         latitude, longitude, contact_name, contact_phone, remark, apply_time)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    """, [
                        name, address, business_hours, 'pending',
                        lat, lng, contact_name, contact_phone, remark, timezone.now()
                    ])
                    
            messages.success(request, '申请已提交，我们将尽快审核')
            return redirect('market:list')
            
        except Exception as e:
            messages.error(request, f'申请提交失败: {str(e)}')
            return render(request, 'market/apply.html', {'form_data': request.POST})
    
    return render(request, 'market/apply.html')


def nearest_markets(request):
    """查找最近的超市"""
    lat = request.GET.get('lat')
    lng = request.GET.get('lng')
    
    if not lat or not lng:
        return JsonResponse({'error': '缺少位置信息'}, status=400)
    
    try:
        lat = float(lat)
        lng = float(lng)
    except ValueError:
        return JsonResponse({'error': '位置信息格式错误'}, status=400)
    
    # 计算距离并找出最近的超市
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT market_id, name, address, image_url, business_hours, rating,
                   SQRT(POWER(latitude - %s, 2) + POWER(longitude - %s, 2)) * 111 AS distance
            FROM tb_supermarket
            WHERE status = 1 AND latitude IS NOT NULL AND longitude IS NOT NULL
            ORDER BY distance ASC
            LIMIT 5
        """, [lat, lng])
        columns = [col[0] for col in cursor.description]
        markets = [dict(zip(columns, row)) for row in cursor.fetchall()]
    
    return JsonResponse({'markets': markets})


def market_map(request):
    """超市地图页面"""
    # 获取所有有坐标的超市
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT market_id, name, address, rating, latitude, longitude, image_url
            FROM tb_supermarket
            WHERE status = 1 AND latitude IS NOT NULL AND longitude IS NOT NULL
        """)
        columns = [col[0] for col in cursor.description]
        markets = [dict(zip(columns, row)) for row in cursor.fetchall()]
    
    context = {
        'markets': markets,
        'map_api_key': 'YOUR_MAP_API_KEY',  # 可以从settings获取
    }
    return render(request, 'market/map.html', context)
